﻿namespace SuperMarieApi.App.Controllers.Framework.DataAccess.SqlBuilder
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Text.RegularExpressions;

    using SuperMarieApi.App.Controllers.Framework.Common;
    using SuperMarieApi.App.Controllers.Framework.DataAccess.Config;

    public class SqlConditionBuilder
    {
        protected string _defaultOrderBy;
        protected string _orderField;
        protected string _orderDirection;
        protected DataCommand _dbCommand;
        private List<Condition> _conditions;
        private Func<object, object> _defaultValueProcessor;
        protected string _wherePlaceHolder;
        protected string _orderByPlaceHolder;

        public SqlConditionBuilder(DataCommand dbCommand)
            : this(dbCommand, "")
        {

        }

        public SqlConditionBuilder(DataCommand dbCommand, string defaultOrderBy)
            : this(dbCommand, "", "", "")
        {

        }

        public SqlConditionBuilder(DataCommand dbCommand, string defaultOrderBy, string orderField, string orderDirection, string wherePlaceHolder = "#StrWhere#", string orderByPlaceHolder = "#OrderBy#")
        {
            this._dbCommand = dbCommand;
            this._defaultOrderBy = defaultOrderBy;
            this._orderField = orderField;
            this._orderDirection = orderDirection;
            this._wherePlaceHolder = wherePlaceHolder;
            this._orderByPlaceHolder = orderByPlaceHolder;

            this._conditions = new List<Condition>();
            this._defaultValueProcessor = (value) =>
            {
                if (value != null)
                {
                    if (value.GetType() == typeof(string))
                    {
                        //空字符串映射为null
                        string strValue = value.ToString().Trim();
                        if (strValue.Length == 0)
                        {
                            strValue = null;
                        }
                        return strValue;
                    }
                }

                return value;
            };
        }

        public void AddCondition(SqlRelation relation, string colName, SqlOperator opName, string paramName, DbType dbType, object value, Func<object, object> valueProcessor)
        {
            //预处理条件值
            valueProcessor = valueProcessor ?? this._defaultValueProcessor;
            value = valueProcessor(value);

            //如果值为null,就不添加这个条件
            if (value == null)
            {
                return;
            }

            if (StringHelper.IsNullOrEmpty(colName))
            {
                throw new ArgumentException("ColumnName cannot be null or empty.");
            }

            if (StringHelper.IsNullOrEmpty(paramName))
            {
                throw new ArgumentException("ParameterName cannot be null or empty.");
            }

            if (opName == SqlOperator.In || opName == SqlOperator.NotIn)
            {
                if (!(value is IEnumerable))
                {
                    throw new ArgumentException("When you do IN/NOT IN query, parameter value must be enumerable.");
                }
            }
            var condtion = new SqlCondition(relation, colName, opName, paramName, dbType, value);
            this._conditions.Add(condtion);
        }

        public void AddCondition(SqlRelation relation, string colName, SqlOperator opName, string paramName, DbType dbType, object value)
        {
            this.AddCondition(relation, colName, opName, paramName, dbType, value, this._defaultValueProcessor);
        }

        public void AddCustomCondition(SqlRelation relation, string customSql, string paramName, DbType dbType, object value, Func<object, object> valueProcessor)
        {
            //预处理条件值
            valueProcessor = valueProcessor ?? this._defaultValueProcessor;
            value = valueProcessor(value);

            //如果值为null,就不添加这个条件
            if (value == null)
            {
                return;
            }

            if (StringHelper.IsNullOrEmpty(customSql))
            {
                throw new ArgumentException("CustomSql cannot be null or empty.");
            }

            var condtion = new CustomSqlCondition(relation, customSql, paramName, dbType, value);
            this._conditions.Add(condtion);
        }

        public void AddCustomCondition(SqlRelation relation, string customSql, string paramName, DbType dbType, object value)
        {
            if (StringHelper.IsNullOrEmpty(paramName))
            {
                throw new ArgumentException("ParameterName cannot be null or empty.");
            }

            this.AddCustomCondition(relation, customSql, paramName, dbType, value, this._defaultValueProcessor);
        }

        public void AddCustomCondition(SqlRelation relation, string customSql)
        {
            var condtion = new CustomSqlCondition(relation, customSql, null, DbType.AnsiString, null);
            this._conditions.Add(condtion);
        }

        public void AddLeftBracketCondition(SqlRelation relation)
        {
            var condtion = new LeftBracketCondition(relation);
            this._conditions.Add(condtion);
        }

        public void AddRightBracketCondition(SqlRelation relation)
        {
            var condtion = new RightBracketCondition(relation);
            this._conditions.Add(condtion);
        }

        public virtual void BuildSql(bool appendWhere = true)
        {
            if (!this.IsBracketValid())
            {
                throw new ArgumentException("条件中出现的括号不匹配。");
            }

            int count = this._conditions.Count;
            string result = string.Empty;
            if (count > 0)
            {
                result += (appendWhere ? " WHERE " : this._conditions[0].GetPrepend()) + this._conditions[0].Build2String(this._dbCommand);

            }

            Condition condtion = null;
            Condition before = null;
            for (int i = 1; i < count; i++)
            {
                condtion = this._conditions[i];
                before = this._conditions[i - 1];
                //如果上一个条件是左括号，则此条件前面不加AND或者OR逻辑运算符
                string sql = condtion.Build2String(this._dbCommand);
                if (!StringHelper.IsNullOrEmpty(sql))
                {
                    result += ((before is LeftBracketCondition || condtion is RightBracketCondition) ? " " : condtion.GetPrepend()) + sql;
                }
            }
            result = Regex.Replace(result, @"\(\s+\)", "");
            if (result.Trim().ToLower() == "where")
            {
                result = "";
            }
            result = this._dbCommand.CommandText.Replace(this._wherePlaceHolder, result);

            //校验order by语句需要的参数
            if (!string.IsNullOrEmpty(this._orderField) && !string.IsNullOrEmpty(this._orderDirection))
            {
                bool isValidOrderField = false;
                if (this._dbCommand.DataCommandConfig.OrderFields != null)
                {
                    OrderFieldConfig orderConfig = null;
                    foreach (var config in this._dbCommand.DataCommandConfig.OrderFields)
                    {
                        if (string.Compare(config.FieldName, this._orderField, true) == 0)
                        {
                            isValidOrderField = true;
                            orderConfig = config;
                            break;
                        }
                    }
                    string orderDirection = this._orderDirection == null ? "desc" : this._orderDirection.ToLower();
                    bool isValidOrderDirection = orderDirection == "asc" || orderDirection == "desc";
                    if (isValidOrderField && isValidOrderDirection)
                    {
                        string column = string.IsNullOrEmpty(orderConfig.ColumnName) ? this._orderField : orderConfig.ColumnName;
                        this._defaultOrderBy = " " + column + " " + this._orderDirection;
                    }
                }
            }
            if (!string.IsNullOrEmpty(this._defaultOrderBy))
            {
                this._defaultOrderBy = " ORDER BY " + this._defaultOrderBy + " ";
            }
            this._dbCommand.CommandText = result.Replace(this._orderByPlaceHolder, this._defaultOrderBy);
        }

        /// <summary>
        /// 验证条件中加的括号是否匹配
        /// </summary>
        /// <returns></returns>
        private bool IsBracketValid()
        {
            if (this._conditions.Count == 0)
            {
                return true;
            }

            Stack<Condition> bracketStack = new Stack<Condition>(this._conditions.Count);
            foreach (var c in this._conditions)
            {
                if (c is LeftBracketCondition)
                {
                    bracketStack.Push(c);
                }
                else if (c is RightBracketCondition)
                {
                    bracketStack.Pop();
                }
            }
            //如果最后栈里面没有元素，则说明左右括号是匹配的
            return bracketStack.Count == 0;
        }
    }
}
